using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using System.Runtime.InteropServices;

namespace PCSComMaterials.Inventory.DS
{
	[Guid("57809A62-AAEB-46ad-BC57-0CA504EDA2CB")]
	public class IV_ItemSerialDS// //: IObjectDS
	{
		public IV_ItemSerialDS()
		{
		}

		private const string THIS = "PCSComMaterials.Inventory.DS.IV_ItemSerialDS";
		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///        IV_ItemSerialVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       HungLa
		///       DungLa: 11-Mar-2005
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///       11-Mar-2005: Check BinID - DungLa
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_ItemSerialVO objObject = (IV_ItemSerialVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO IV_ItemSerial("
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.QASTATUS_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.QASTATUS_FLD].Value = objObject.QAStatus;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFNO_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFNO_FLD].Value = objObject.RefNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFLINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFLINE_FLD].Value = objObject.RefLine;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.INSPSTATUS_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.INSPSTATUS_FLD].Value = objObject.InspStatus;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MRBDISPLAY_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MRBDISPLAY_FLD].Value = objObject.MRBDisplay;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REMAIN_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_ItemSerialTable.REMAIN_FLD].Value = objObject.Remain;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = objObject.Serial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.TRANTYPEID_FLD, OleDbType.Integer));
				if (objObject.TranTypeID > 0)
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.TRANTYPEID_FLD].Value = objObject.TranTypeID;
				}
				else 
					ocmdPCS.Parameters[IV_ItemSerialTable.TRANTYPEID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOCATIONID_FLD].Value = objObject.LocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.BINID_FLD, OleDbType.Integer));
				if (objObject.BinID > 0)
					ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = objObject.BinID;
				else
					ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///        IV_ItemSerialVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void AddReturnedGoods(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".AddReturnedGoods()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				IV_ItemSerialVO objObject = (IV_ItemSerialVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO IV_ItemSerial("
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD + ")"
					+ "VALUES(?,?,?,?,?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.QASTATUS_FLD, OleDbType.Integer));
				if (objObject.QAStatus > 0)
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.QASTATUS_FLD].Value = objObject.QAStatus;
				}
				else
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.QASTATUS_FLD].Value = DBNull.Value;
				}

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFNO_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFNO_FLD].Value = objObject.RefNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFLINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFLINE_FLD].Value = objObject.RefLine;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = objObject.Serial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.TRANTYPEID_FLD, OleDbType.Integer));
				if (objObject.TranTypeID > 0)
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.TRANTYPEID_FLD].Value = objObject.TranTypeID;
				}
				else
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.TRANTYPEID_FLD].Value = DBNull.Value;
				}

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOCATIONID_FLD].Value = objObject.LocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.BINID_FLD, OleDbType.Integer));
				if (objObject.BinID > 0)
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = objObject.BinID;
				}
				else
				{
					ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = DBNull.Value;
				}

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to delete data from IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       void
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + IV_ItemSerialTable.TABLE_NAME + " WHERE  " + "ItemSerialID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_ItemSerialVO
		///    </Outputs>
		///    <Returns>
		///       IV_ItemSerialVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_ItemSerialTable.ITEMSERIALID_FLD + ","
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.ITEMSERIALID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				IV_ItemSerialVO objObject = new IV_ItemSerialVO();

				while (odrPCS.Read())
				{
					objObject.ItemSerialID = int.Parse(odrPCS[IV_ItemSerialTable.ITEMSERIALID_FLD].ToString().Trim());
					objObject.Lot = odrPCS[IV_ItemSerialTable.LOT_FLD].ToString().Trim();
					objObject.QAStatus = int.Parse(odrPCS[IV_ItemSerialTable.QASTATUS_FLD].ToString().Trim());
					objObject.RefNo = int.Parse(odrPCS[IV_ItemSerialTable.REFNO_FLD].ToString().Trim());
					objObject.RefLine = int.Parse(odrPCS[IV_ItemSerialTable.REFLINE_FLD].ToString().Trim());
					objObject.InspStatus = int.Parse(odrPCS[IV_ItemSerialTable.INSPSTATUS_FLD].ToString().Trim());
					objObject.MRBDisplay = int.Parse(odrPCS[IV_ItemSerialTable.MRBDISPLAY_FLD].ToString().Trim());
					objObject.Remain = bool.Parse(odrPCS[IV_ItemSerialTable.REMAIN_FLD].ToString().Trim());
					objObject.Serial = odrPCS[IV_ItemSerialTable.SERIAL_FLD].ToString().Trim();
					objObject.CCNID = int.Parse(odrPCS[IV_ItemSerialTable.CCNID_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[IV_ItemSerialTable.PRODUCTID_FLD].ToString().Trim());
					objObject.TranTypeID = int.Parse(odrPCS[IV_ItemSerialTable.TRANTYPEID_FLD].ToString().Trim());
					objObject.LocationID = int.Parse(odrPCS[IV_ItemSerialTable.LOCATIONID_FLD].ToString().Trim());
					objObject.BinID = int.Parse(odrPCS[IV_ItemSerialTable.BINID_FLD].ToString().Trim());
					objObject.MasterLocationID = int.Parse(odrPCS[IV_ItemSerialTable.MASTERLOCATIONID_FLD].ToString().Trim());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       IV_ItemSerialVO
		///    </Outputs>
		///    <Returns>
		///       IV_ItemSerialVO
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(string pstrSerial, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_ItemSerialTable.ITEMSERIALID_FLD + ","
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "='" + pstrSerial + "'"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "=" + pintCCNID
					+ " AND " + IV_ItemSerialTable.PRODUCTID_FLD + "=" + pintProductID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				IV_ItemSerialVO objObject = new IV_ItemSerialVO();

				while (odrPCS.Read())
				{
					if (odrPCS[IV_ItemSerialTable.ITEMSERIALID_FLD] != DBNull.Value)
						objObject.ItemSerialID = int.Parse(odrPCS[IV_ItemSerialTable.ITEMSERIALID_FLD].ToString().Trim());
					objObject.Lot = odrPCS[IV_ItemSerialTable.LOT_FLD].ToString().Trim();
					if (odrPCS[IV_ItemSerialTable.QASTATUS_FLD] != DBNull.Value)
						objObject.QAStatus = int.Parse(odrPCS[IV_ItemSerialTable.QASTATUS_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.REFNO_FLD] != DBNull.Value)
						objObject.RefNo = int.Parse(odrPCS[IV_ItemSerialTable.REFNO_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.REFLINE_FLD] != DBNull.Value)
						objObject.RefLine = int.Parse(odrPCS[IV_ItemSerialTable.REFLINE_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.INSPSTATUS_FLD] != DBNull.Value)
						objObject.InspStatus = int.Parse(odrPCS[IV_ItemSerialTable.INSPSTATUS_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.MRBDISPLAY_FLD] != DBNull.Value)
						objObject.MRBDisplay = int.Parse(odrPCS[IV_ItemSerialTable.MRBDISPLAY_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.REMAIN_FLD] != DBNull.Value)
						objObject.Remain = bool.Parse(odrPCS[IV_ItemSerialTable.REMAIN_FLD].ToString().Trim());
					objObject.Serial = odrPCS[IV_ItemSerialTable.SERIAL_FLD].ToString().Trim();
					if (odrPCS[IV_ItemSerialTable.CCNID_FLD] != DBNull.Value)
						objObject.CCNID = int.Parse(odrPCS[IV_ItemSerialTable.CCNID_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.PRODUCTID_FLD] != DBNull.Value)
						objObject.ProductID = int.Parse(odrPCS[IV_ItemSerialTable.PRODUCTID_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.TRANTYPEID_FLD] != DBNull.Value)
						objObject.TranTypeID = int.Parse(odrPCS[IV_ItemSerialTable.TRANTYPEID_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.LOCATIONID_FLD] != DBNull.Value)
						objObject.LocationID = int.Parse(odrPCS[IV_ItemSerialTable.LOCATIONID_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.BINID_FLD] != DBNull.Value)
						objObject.BinID = int.Parse(odrPCS[IV_ItemSerialTable.BINID_FLD].ToString().Trim());
					if (odrPCS[IV_ItemSerialTable.MASTERLOCATIONID_FLD] != DBNull.Value)
						objObject.MasterLocationID = int.Parse(odrPCS[IV_ItemSerialTable.MASTERLOCATIONID_FLD].ToString().Trim());

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///       IV_ItemSerialVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			IV_ItemSerialVO objObject = (IV_ItemSerialVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_ItemSerial SET "
					+ IV_ItemSerialTable.LOT_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.REFNO_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.REFLINE_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.REMAIN_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.SERIAL_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.CCNID_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.BINID_FLD + "=   ?" + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD + "=  ?"
					+ " WHERE " + IV_ItemSerialTable.ITEMSERIALID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOT_FLD].Value = objObject.Lot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.QASTATUS_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.QASTATUS_FLD].Value = objObject.QAStatus;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFNO_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFNO_FLD].Value = objObject.RefNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REFLINE_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.REFLINE_FLD].Value = objObject.RefLine;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.INSPSTATUS_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.INSPSTATUS_FLD].Value = objObject.InspStatus;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MRBDISPLAY_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MRBDISPLAY_FLD].Value = objObject.MRBDisplay;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REMAIN_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_ItemSerialTable.REMAIN_FLD].Value = objObject.Remain;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = objObject.Serial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.TRANTYPEID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.TRANTYPEID_FLD].Value = objObject.TranTypeID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOCATIONID_FLD].Value = objObject.LocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.BINID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = objObject.BinID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.ITEMSERIALID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.ITEMSERIALID_FLD].Value = objObject.ItemSerialID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///       IV_ItemSerialVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateReturnGoods(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".UpdateReturnGoods()";

			IV_ItemSerialVO objObject = (IV_ItemSerialVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_ItemSerial SET "
					+ IV_ItemSerialTable.REMAIN_FLD + "=   ?"
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "=   ?"
					+ " AND " + IV_ItemSerialTable.PRODUCTID_FLD + "=   ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REMAIN_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_ItemSerialTable.REMAIN_FLD].Value = objObject.Remain;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = objObject.Serial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = objObject.CCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get all data from IV_ItemSerial
		///    </Description>
		///    <Inputs>
		///               
		///    </Inputs>
		///    <Outputs>
		///       DataSet
		///    </Outputs>
		///    <Returns>
		///       DataSet
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_ItemSerialTable.ITEMSERIALID_FLD + ","
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, IV_ItemSerialTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to update a DataSet
		///    </Description>
		///    <Inputs>
		///        DataSet       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       HungLa
		///    </Authors>
		///    <History>
		///       Wednesday, February 23, 2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ IV_ItemSerialTable.ITEMSERIALID_FLD + ","
					+ IV_ItemSerialTable.LOT_FLD + ","
					+ IV_ItemSerialTable.QASTATUS_FLD + ","
					+ IV_ItemSerialTable.REFNO_FLD + ","
					+ IV_ItemSerialTable.REFLINE_FLD + ","
					+ IV_ItemSerialTable.INSPSTATUS_FLD + ","
					+ IV_ItemSerialTable.MRBDISPLAY_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.CCNID_FLD + ","
					+ IV_ItemSerialTable.PRODUCTID_FLD + ","
					+ IV_ItemSerialTable.TRANTYPEID_FLD + ","
					+ IV_ItemSerialTable.LOCATIONID_FLD + ","
					+ IV_ItemSerialTable.BINID_FLD + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD
					+ "  FROM " + IV_ItemSerialTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, IV_ItemSerialTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
				}

				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to get data for Serial dropdown
		///    </Description>
		///    <Inputs>
		///        int
		///    </Inputs>
		///    <Outputs>
		///       DataTable
		///    </Outputs>
		///    <Returns>
		///       DataTable
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       17-Feb-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataTable GetDataForTrueDBDropDown(string pstrLOT)
		{
			const string METHOD_NAME = THIS + ".GetDataForSerialDropDown()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ IV_ItemSerialTable.ITEMSERIALID_FLD + ","
					+ IV_ItemSerialTable.SERIAL_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME;
				if ((pstrLOT != null) && (pstrLOT != string.Empty))
					strSql += " WHERE " + IV_ItemSerialTable.LOT_FLD + "='" + pstrLOT + "'";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, MST_MasterLocationTable.TABLE_NAME);

				return dstPCS.Tables[MST_MasterLocationTable.TABLE_NAME];
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to check for existing Product of Serial in CCN
		///    </Description>
		///    <Inputs>
		///        CCNID, Serial, product ID
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       true if have
		///       false if not
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool HasProduct(string pstrSerial, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".HasProduct()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT COUNT(*) "
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.PRODUCTID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = pstrSerial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = pintProductID;


				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (int.Parse(objResult.ToString()) > 0)
					{
						return true;
					}
					else
					{
						return false;
					}
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///       This method uses to check for existing Serial
		///    </Description>
		///    <Inputs>
		///        pstrSerial, CCNID
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       true if existed
		///       false if not
		///    </Returns>
		///    <Authors>
		///       DungLa
		///    </Authors>
		///    <History>
		///       11-Mar-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool HasSerial(string pstrSerial, int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".HasSerial()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT COUNT(*) "
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = pstrSerial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (int.Parse(objResult.ToString()) > 0)
					{
						return true;
					}
					else
					{
						return false;
					}
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		//**************************************************************************              
		///    <Description>
		///     This method uses to check remain product by serial
		///    </Description>
		///    <Inputs>
		///        string Serial, int CCNID, int ProductID
		///    </Inputs>
		///    <Outputs>
		///      void
		///    </Outputs>
		///    <Returns>
		///      void
		///    </Returns>
		///    <Authors>
		///       DungLA
		///    </Authors>
		///    <History>
		///    21-Apr-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool CheckRemain(string pstrSerial, int pintCCNID, int pintProductID)
		{
			const string METHOD_NAME = THIS + ".CheckRemain()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ IV_ItemSerialTable.REMAIN_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.PRODUCTID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = pstrSerial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = pintProductID;

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult == null)
				{
					return false;
				}
				else
				{
					if (objResult != DBNull.Value)
					{
						return bool.Parse(objResult.ToString());
					}
					else
						return false;
				}

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		//**************************************************************************              
		///    <Description>
		///     This method uses to update remain of serial
		///    </Description>
		///    <Inputs>
		///        string Serial, int CCNID, int ProductID
		///    </Inputs>
		///    <Outputs>
		///      void
		///    </Outputs>
		///    <Returns>
		///      void
		///    </Returns>
		///    <Authors>
		///       DungLA
		///    </Authors>
		///    <History>
		///    21-Apr-2005
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public bool UpdateRemain(string pstrSerial, int pintCCNID, int pintProductID, bool pblnRemain)
		{
			const string METHOD_NAME = THIS + ".UpdateRemain()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "UPDATE " + IV_ItemSerialTable.TABLE_NAME + " SET "
					+ IV_ItemSerialTable.REMAIN_FLD + "= ?"
					+ " WHERE " + IV_ItemSerialTable.SERIAL_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.CCNID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.PRODUCTID_FLD + "= ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.REMAIN_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[IV_ItemSerialTable.REMAIN_FLD].Value = pblnRemain;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = pstrSerial;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.CCNID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.CCNID_FLD].Value = pintCCNID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.PRODUCTID_FLD].Value = pintProductID;

				ocmdPCS.Connection.Open();
				int intEffectedRow = ocmdPCS.ExecuteNonQuery();
				oconPCS = null;
				return (intEffectedRow == 0) ? false : true;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		/// <summary>
		/// - Check if Item has Serial then return true, Serial number and the Remain of Serial
		/// </summary>
		public bool HasSerial(int pintProductID, string pstrLot, out string ostrSerial, out bool oblnRemain)
		{
			const string METHOD_NAME = THIS + ".HasSerial()";
			DataSet dstPCS = new DataSet();
		
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ IV_ItemSerialTable.SERIAL_FLD + ","
					+ IV_ItemSerialTable.REMAIN_FLD
					+ " FROM " + IV_ItemSerialTable.TABLE_NAME
					+ " WHERE " + IV_ItemSerialTable.PRODUCTID_FLD + " = " + pintProductID
					+ " AND " + IV_ItemSerialTable.LOT_FLD + " = ?";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Parameters.Add(IV_ItemSerialTable.LOT_FLD, OleDbType.VarWChar);
				ocmdPCS.Parameters[IV_ItemSerialTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Connection.Open();
				OleDbDataReader odrd = ocmdPCS.ExecuteReader();
				while (odrd.Read())
				{
					ostrSerial = odrd[IV_ItemSerialTable.SERIAL_FLD].ToString();
					if (odrd[IV_ItemSerialTable.REMAIN_FLD].ToString() != string.Empty)
					{
						oblnRemain = bool.Parse(odrd[IV_ItemSerialTable.REMAIN_FLD].ToString());
					}
					else
						oblnRemain = false;
					return true;
				}
				ostrSerial = string.Empty;
				oblnRemain = false;
				return false;				
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		/// <summary>
		/// Move ItemSerial from SourceLocation/Bin to DestLocation/Bin
		/// If BinID > 0 then move Item Serial to Bin, else move Item Serial to Location
		/// </summary>
		public void MoveSerial(int pintCCNID, int pintSourceMasLocID, int pintSourceLocationID, int pintSourceBinID, int pintDestMasLocID, int pintDestLocationID, int pintDestBinID, int pintProductID, string pstrLot, string pstrSerial)
		{
			const string METHOD_NAME = THIS + ".MoveSerial()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE IV_ItemSerial SET "
					+ IV_ItemSerialTable.LOCATIONID_FLD + "=  " + pintDestLocationID + ","
					+ IV_ItemSerialTable.BINID_FLD + "=   " + pintDestBinID + ","
					+ IV_ItemSerialTable.MASTERLOCATIONID_FLD + "=  " + pintDestMasLocID
					+ " WHERE " + IV_ItemSerialTable.MASTERLOCATIONID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.LOCATIONID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.BINID_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.LOT_FLD + "= ?"
					+ " AND " + IV_ItemSerialTable.SERIAL_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.MASTERLOCATIONID_FLD].Value = pintSourceMasLocID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOCATIONID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOCATIONID_FLD].Value = pintSourceLocationID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.BINID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[IV_ItemSerialTable.BINID_FLD].Value = pintSourceBinID;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.LOT_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.LOT_FLD].Value = pstrLot;

				ocmdPCS.Parameters.Add(new OleDbParameter(IV_ItemSerialTable.SERIAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[IV_ItemSerialTable.SERIAL_FLD].Value = pstrSerial;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
				{
					throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
				}
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}